
using System;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;

namespace Common
{
	/// <summary>
	/// Description of DiagnoseSymptomDao.
	/// </summary>
	public class DiagnoseSymptomDao
	{
        private DiagnoseSymptomDao()
		{
		}
		
		public static int insert(DiagnoseSymptom dd)
		{
            string sql = "insert into DiagnoseSymptom(mstr_id,symptom_id,result)values(?,?,?);";
			OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = dd.MstrId;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = dd.SymptomId;
            parameters[2] = new OleDbParameter("@result", OleDbType.VarChar);
            parameters[2].Value = dd.Result;
			
			return AccessDBUtil.ExecuteInsert(sql,parameters);
		}

        public static int update(DiagnoseSymptom dd)
		{
            string sql = "update DiagnoseSymptom set symptom_id=?,result=? where id=?";
			OleDbParameter[] parameters = new OleDbParameter[3];
            parameters[0] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[0].Value = dd.SymptomId;
            parameters[1] = new OleDbParameter("@result", OleDbType.VarChar);
            parameters[1].Value = dd.Result;
			parameters[2] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[2].Value=dd.Id;
			
			return AccessDBUtil.ExecuteNonQuery(sql,parameters);
			
			
		}
        public static DiagnoseSymptom getById(int id)
		{
            
            string sql = "select dd.*,s.kind,s.state,s.part,s.complexity from DiagnoseSymptom dd inner join Symptom s on dd.symptom_id = s.id  where dd.id=?";
			OleDbParameter[] parameters = new OleDbParameter[1];
			parameters[0] = new OleDbParameter("@id",OleDbType.Integer);
			parameters[0].Value=id;
			DataSet ds = AccessDBUtil.ExecuteQuery(sql,parameters);
            if (ds.Tables["ds"].Rows.Count == 0) return null;
			return Row2DiagnoseSymptom(ds.Tables["ds"].Rows[0]);
		}

        public static int getCountBySymptomId(int mstr_id, int symptom_id)
        {

            string sql = "select * from DiagnoseSymptom where mstr_id = ? and symptom_id = ?";
            OleDbParameter[] parameters = new OleDbParameter[2];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            parameters[1] = new OleDbParameter("@symptom_id", OleDbType.Integer);
            parameters[1].Value = symptom_id;

            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            return ds.Tables["ds"].Rows.Count;

        }

        public static int deleteById(int id)
        {
            string sql = "delete from DiagnoseSymptom where id=?";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@id", OleDbType.Integer);
            parameters[0].Value = id;
            return AccessDBUtil.ExecuteNonQuery(sql, parameters);
        }

        private static DiagnoseSymptom Row2DiagnoseSymptom(DataRow row)
		{
            DiagnoseSymptom dd = new DiagnoseSymptom();
            dd.Id = Int32.Parse(row["id"].ToString());
            dd.MstrId = Int32.Parse(row["mstr_id"].ToString());
            dd.SymptomId = Int32.Parse(row["symptom_id"].ToString());
            dd.Result = row["result"].ToString();
            dd.Kind = row["kind"].ToString();
            dd.Complexity = row["complexity"].ToString();
            dd.Part = row["part"].ToString();
            dd.State = row["state"].ToString();
            return dd;
		}

        public static List<DiagnoseSymptom> getAllDiagnoseSymptoms(int mstr_id)
        {
            string sql = "select dd.*,s.kind,s.state,s.part,s.complexity from DiagnoseSymptom dd inner join Symptom s on dd.symptom_id = s.id where dd.mstr_id=? order by dd.id";
            OleDbParameter[] parameters = new OleDbParameter[1];
            parameters[0] = new OleDbParameter("@mstr_id", OleDbType.Integer);
            parameters[0].Value = mstr_id;
            DataSet ds = AccessDBUtil.ExecuteQuery(sql, parameters);

            List<DiagnoseSymptom> cds = new List<DiagnoseSymptom>();
            for (int i = 0; i < ds.Tables["ds"].Rows.Count; i++)
            {
                cds.Add(Row2DiagnoseSymptom(ds.Tables["ds"].Rows[i]));
            }
            return cds;
        }
		
	}
}
